﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;

namespace BLL.Services.Authors
{
    using BLL.Services.Authors.Listing;

    public class AuthorsCollection
    {
        private readonly string _connectionString;

        public AuthorsCollection(string connectionString)
        {
            _connectionString = connectionString;
        }

        /// <summary>
        /// Creates the specified model.
        /// </summary>
        /// <param name="model">The model.</param>
        public void Create(Author model)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Authors.Insert(conn, model.LastName, model.FirstName, model.MiddleName);
                cmd.ExecuteNonQuery();
            }
        }

        public bool Validate(int authorId, string firstName, string lastName, string middleName)
        {
            var result = false;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Authors.Validate(conn, authorId, firstName, lastName, middleName);

                var isValidParameter = new SqlParameter("@valid", SqlDbType.Bit, 4) { Direction = ParameterDirection.ReturnValue };
                cmd.Parameters.Add(isValidParameter);

                cmd.ExecuteNonQuery();
                result = (int)isValidParameter.Value == 1;
            }
            return result;
        }

        /// <summary>
        /// Deletes the specified author id.
        /// </summary>
        /// <param name="authorId">The author id.</param>
        public void Delete(int authorId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Authors.Delete(conn, authorId);
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Gets the listing author.
        /// </summary>
        /// <param name="page">The page.</param>
        /// <param name="size">The size.</param>
        /// <param name="column">The column.</param>
        /// <param name="direction">The direction.</param>
        /// <param name="filter">The filter.</param>
        public AuthorListing GetListingAuthor(
            int page, int size, string column, int direction, AuthorFilter filter)
        {
            if (filter == null)
                filter = new AuthorFilter();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Authors.GetListing(
                    conn, page, size, column, direction, filter.FirstName, filter.LastName);

                var totalCount = new SqlParameter("@TotalCount", SqlDbType.Int, 4)
                    { Direction = ParameterDirection.ReturnValue };
                cmd.Parameters.Add(totalCount);

                var result = new AuthorListing
                    {
                        Page = page,
                        Size = size,
                        Direction = direction,
                        Column = column,
                        Filter = filter
                    };

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = new AuthorListing.Author();
                        item.AuthorId = reader.GetInt32(0);
                        item.FirstName = reader.GetString(1);
                        item.LastName = reader.GetString(2);
                        item.CountBooks = reader.GetInt32(3);
                        result.Authors.Add(item);
                    }
                }
                result.TotalPages = (int)totalCount.Value % size == 0
                                        ? (int)totalCount.Value / size
                                        : ((int)totalCount.Value / size) + 1;
                return result;
            }
        }

        /// <summary>
        /// Saves the specified model.
        /// </summary>
        /// <param name="model">The model.</param>
        public void Save(Author model)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Authors.Update(conn, model.AuthorId, model.LastName, model.FirstName, model.MiddleName);
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Selects the by id.
        /// </summary>
        /// <param name="authorId">The author id.</param>
        public Author SelectById(int authorId)
        {
            Author result = null;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Authors.SelectById(conn, authorId);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                        result = GetAuthor(reader);
                }
            }
            return result;
        }

        public int GetAuthorId(string firstName, string lastName, string middleName)
        {
            int authorId = 0;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Authors.GetId(conn, firstName, lastName, middleName);

                var authorIdParameter = new SqlParameter("@authorId", SqlDbType.Int, 10) { Direction = ParameterDirection.ReturnValue };
                cmd.ExecuteNonQuery();

                authorId = (int)authorIdParameter.Value;
            }
            return authorId;
        }

        public IEnumerable<Author> GetAuthorsByLetter(string letter)
        {
            var result = new List<Author>();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Authors.GetByLetter(conn, letter);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = GetAuthor(reader);
                        result.Add(item);
                    }
                }
                return result;
            }
        }

        public IEnumerable<Author> GetAuthotsByIds(int [] authorsIds)
        {
            var result = new List<Author>();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var authorsIdsTable = new DataTable("list_author");
                authorsIdsTable.Columns.Add("AuthorId", typeof(int));
                foreach (var id in authorsIds)
                {
                    authorsIdsTable.Rows.Add(id);
                }

                var cmd = new SqlCommand("authors_get_by_ids", conn)
                    { CommandType = CommandType.StoredProcedure };
                var authorsIdsParameter = new SqlParameter("@authorsIds", SqlDbType.Structured)
                    {
                        Value = authorsIdsTable
                    };
                cmd.Parameters.Add(authorsIdsParameter);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var item = GetAuthor(reader);
                        result.Add(item);
                    }
                }
            }
            return result;
        }

        /// <summary>
        /// Get author from data reader
        /// </summary>
        /// <param name="reader">The reader.</param>
        private static Author GetAuthor(SqlDataReader reader)
        {
            var author = new Author();
            author.AuthorId = reader.GetInt32(0);
            author.FirstName = reader[1] as string;
            author.LastName = reader[2] as string;
            author.MiddleName = reader[3] as string;
            return author;
        }
    }
}